﻿using System;
//using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using prjKQXS_DLL;

namespace prjKQXS_DLL
{
    public class DungChung2_DAL
    {

        public DataTable loadData(string tableName, bool isPublic)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("Select * from [" + tableName + "] Where isPublic=@isPublic", conn);
            sqlCommand.Parameters.AddWithValue("@isPublic", isPublic);
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            DataTable dt = new DataTable();
            da.Fill(dt);
            try
            {
                return dt;
            }
            catch
            {
                throw;
            }
            finally
            {
                sqlCommand.Dispose();
                conn.Close();
            }
        }

        public int updateCCDC(string tableName,string dvtinh, int id)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("Update ["+tableName+"] set DV_tinh=@dvtinh Where ID=@id", conn);
            cmd.Parameters.AddWithValue("@dvtinh", dvtinh);
            cmd.Parameters.AddWithValue("@id", id);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int updateTSCD(string tableName, string dvtinh, int id)
        {
            SqlConnection conn =new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("Update [" + tableName + "] set DV_Tinh=@dvtinh Where ID=@id", conn);
            cmd.Parameters.AddWithValue("@dvtinh", dvtinh);
            cmd.Parameters.AddWithValue("@id", id);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public string FirstChar(string sotk)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Select LEFT(mau,1) from BangTK Where SO_TK=@sotk and isPublic=1 ", conn);
                cmd.Parameters.AddWithValue("@sotk", sotk);                
                try
                {
                    return cmd.ExecuteScalar().ToString();
                }
                catch (Exception)
                {
                    throw;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int updateRows(string tableName, DungChung_BLL2_bean dungchung2_bean)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("Update [" + tableName + "] set ma_dt=@ma_dt,ten_dt=@ten_dt Where ID=@ID", conn);
            cmd.Parameters.AddWithValue("@ma_dt", dungchung2_bean.ma_dt);
            cmd.Parameters.AddWithValue("@ten_dt", dungchung2_bean.ten_dt);
            cmd.Parameters.AddWithValue("@ID", dungchung2_bean.ID);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int deleteRows(string tableName, DungChung_BLL2_bean dungchung2_bean)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand command = new SqlCommand("Delete from [" + tableName + "] Where ID=@ID", conn);
            command.Parameters.AddWithValue("@ID", dungchung2_bean.ID);
            try
            {
                return command.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int insertTen_dt(string ma_dt, string ten_dt)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand command = new SqlCommand("Insert into DMDVTinh(ma_dt,ten_dt) values(@ma_dt,@ten_dt)", conn);
            command.Parameters.AddWithValue("@ma_dt", ma_dt);
            command.Parameters.AddWithValue("@ten_dt", ten_dt);
            try
            {
                return command.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public int insertRows(string tableName, DungChung_BLL2_bean dungchung2_bean)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand command = new SqlCommand("Insert into [" + tableName + "](ma_dt,ten_dt) values(@ma_dt,N'Nhập giá trị mới')", conn);
            command.Parameters.AddWithValue("@ma_dt", dungchung2_bean.ma_dt);
            try
            {
                return command.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
